package taskmaster.job;

import taskmaster.auth.User;
import taskmaster.util.LookupLists;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Hashtable;
import java.util.Set;

/**
 * taskmaster.job.TaskBuilder
 * <BR><BR>
 * TaskBuilder builds collections of Tasks for the user based on their request.
 * TaskBuilder builds the tree of all Tasks in Taskmaster, updates those Tasks with their statuses,
 * limits the full list to only the ones the user wants, then returns a TaskCollection with that data.<BR>
 * <BR>
 * There is one public method getTaskCollection() that manages this process, calling all the private methods as needed.<BR>
 * The calling class (ViewTasks) need only worry about sending TaskBuilder a QueryParams object that itself defines what Tasks the user wants to see,
 * and the TaskCollection object into which the resulting tree should be put.<BR>
 * <BR>
 * There are internal data stored in TaskBuilder, such as prepared statements, all the tasks in TMS in a hashtable, and the QueryParams object it last used to create the TaskCollection.
 * When getTaskCollection() is called, TaskBuilder looks to see if the QueryParams object sent is the same as the one that built built the Collection in the first place.
 * This comparison looks for the same object, not the same data.  So a new query that creates a new QueryParams but asks for exactly the same data does not match.
 * If it is the same QueryParams object, then all Tasks in tms are updated with their current status.
 * Otherwise, the whole process is done as though for the first time; building the tree, limiting for user, etc.
 * Once an update is called it is returned after an error check.  If there are errors found, the tree is recreated as though for the first time.
 * In testing, this works pretty well; returing a the correct TaskCollection to the user even though an error occured.
 * Check stderr for notes on what happened...I've found that when I was playing with the update methods things would appear correct but the stderror would show the whole tree being recreated.
 * <BR><BR>
 * The update mechanism is an optimization that saves a whole lot of processing and probably some amount of space.
 * It speeds that whole part of the process by a factor of 4 or so, but don't get too excited, the whole thing seems to take less than a second to complete.<BR>
 * <BR>
 * (What takes a long time, it seems, is writing the output to html...)
 * <BR><BR>
 * <B>DS updated this 030305 to include 'iteration' of jobs.</B>
 * <BR>
 * This implements iterations of jobs in a hacky kind of way.  See below for details.  It should work through the use of a job_states table, so a full snapshot can be made of historical dates.  As it is now some guesses have to be made.
 * <BR>
 * <BR>
 * If state exists for Task and run_date is today - iteration taken from jobs>iteration field matched against task_states>iteration field<BR>
 * If state exists for Task and run_date is NOT today - iteration field ignored (this is what creates multiple iterations on same screen, as though they're different tasks<BR>
 * If state DOESN'T exist for Task - iteration taken from jobs>iteration field, which is today's iteration (there should be a record for each day, not just 1 overall)<BR>
 * <BR>
 * For Inserts into DB - uses Task's iteration as set from above<BR>
 * For updates into DB - doesn't change field; matches on most recent on run date of Task's set (as set above) iteration
 * <BR><BR>
 * Viewing today's Tasks will always reflect accurate iteration; tasks with states will have accurate iteration because jobs table has correct value, tasks without states will correctly assume iteration in jobs table.
 * Viewing non-today dates can rseult in a jumble of iterations seen at once on the same screen because <BR>
 * - Iteration field is ignored for Tasks with states on non-today date (picks most recent on that run_date)<BR>
 * - iteration field is matched with TODAY'S iteration field in jobs table.
 *
 * @see TaskCollection
 */
public class TaskBuilder extends taskmaster.util.RunStatement
{

    private LookupLists ll = null;

    private Hashtable<String, Task> allTaskHashSaved = null;

    public TaskBuilder(LookupLists ll) throws Exception
    {
        this.ll = ll;
    }

    /**
     * Constructs an sql statement (as a string) that will get all the tasks in the entire TMS system.
     * <BR><BR>
     * The selection can be for all the Tasks in tms without looking at their states,
     * or all the tasks that have states for a particular run_date.
     * <BR><BR>
     * This resulting string is intended to be used as the sql for a PreparedStatement. <BR>
     * It includes one '?' for run_date to be filled in when the statement is executed.
     *
     * @param getOnlyWithStates if true, returned string will be sql appropriate for finding Tasks that have states on a particular run_date.  If false, sql will be appropriate for finding all tasks in tms altogether.
     * @param showDeleted       if true, tasks marked as deleted are included in the query, if false, they're specifically not selected.  This is generally false.
     * @return sql
     */
    private String buildTaskCollectionSql(boolean getOnlyWithStates, boolean showDeleted)
    {
        StringBuffer buf = new StringBuffer("");
        buf.append("SELECT ");
        buf.append("t.task_tag AS TASK_TAG, ");
        buf.append("t.job_tag AS JOB_TAG, ");
        buf.append("t.role_id AS ROLE_ID, ");
        buf.append("t.type AS TYPE, ");
        if (getOnlyWithStates)
        {
            buf.append("s1.start_time AS START_TIME, ");
            buf.append("s1.end_time AS END_TIME, ");
            buf.append("sysdate AS CURRENT_TIME, ");
            buf.append("s1.status_id AS STATUS_ID, ");
            buf.append("s1.source AS SOURCE, ");    // source is the user who put this record into the db
            buf.append("s1.host AS HOST, ");
            buf.append("s1.note AS NOTE, ");
            buf.append("s1.iteration as ITERATION, "); // DS added 030305
        } else
        {// DS added 030305
            buf.append("j.iteration as ITERATION, "); // DS added 030305
        }
        buf.append("t.description as DESCRIPTION, ");
        buf.append("t.command AS COMMAND, ");
        buf.append("t.predecessor_tag AS PREDECESSOR_TAG, ");
        buf.append("t.start_time AS CRON_START_TIME, ");            // BEWARE NAME COLLISION WITH TASK_STATES.START_TIME
        buf.append("t.timeout as TIMEOUT, ");
        buf.append("t.retries as RETRIES ");
        buf.append("FROM ");
        if (getOnlyWithStates)
        {
            buf.append("tms.task_states s1, ");
        }
        // START SECTION (1 of 2) darrell changed 021224 - if rundate is current day, give tms.job table's recorded current iteration
        // if rundate is non-today, do as did before and display latest iteration for the task.
        buf.append("tms.jobs j, ");
        // END SECTION (1 of 2) darrell changed 021224
        buf.append("tms.tasks t ");
        buf.append("WHERE ");
        if (!showDeleted)
        {
            buf.append("t.type != 'D' AND ");
        }
        if (getOnlyWithStates)
        {
            buf.append("t.job_tag = s1.job_tag AND ");
            buf.append("t.task_tag = s1.task_tag AND ");
            buf.append("s1.start_time = ( ");
            buf.append("SELECT ");
            buf.append("max(start_time) ");
            buf.append("FROM ");
            buf.append("tms.task_states s2 ");
            buf.append("WHERE ");
            buf.append("s2.job_tag = s1.job_tag AND ");
            buf.append("s2.task_tag = s1.task_tag AND ");
            buf.append("s2.run_date = s1.run_date ");
            buf.append(") ");
        } else
        {
            buf.append("t.job_tag = t.job_tag ");
        }
        if (getOnlyWithStates)
        {
            buf.append("AND ");
            buf.append("s1.run_date = ? ");

            // START SECTION (2 of 2) darrell changed 021224 - if rundate is current day, give tms.job table's recorded current iteration
            // if rundate is non-today, do as did before and display latest iteration for the task.
            buf.append("AND j.iteration = DECODE(s1.run_date, TRUNC(sysdate), s1.iteration, j.iteration) ");
            buf.append("AND j.JOB_TAG = s1.JOB_TAG ");
            // END SECTION (2 of 2) darrell changed 021224
        } else
        {
            buf.append("AND j.JOB_TAG = t.job_tag ");
        }
        //buf.append("order by t.priority");
        System.out.println(buf.toString());
        return buf.toString();
    }


    /**
     * Constructs an sql statement (as a string) that will get a list of job_tags & task_tags that match the parameters passed to it.
     * <BR><BR>Used to determine which Tasks fit a user's request.
     * <BR><BR>
     * Can limit query by any of these fields;
     * <UL>
     * <LI>Any specific role(s) -- (such as equities_desk)
     * <LI>Any specific job(s) -- (such as EQ_EOD)
     * <LI>Any specific status('s) -- (such as 1, 2, 3, -2)
     * <LI>Any specific type(s) -- (such as D, X, E)
     * <LI>Any specific date -- (such as 04/26/02)
     * </UL>
     * <p/>
     * If a field is null, the query is not limited to that field at all.
     */
    private String buildTaskIdSql(int[] limitToRole,
                                  String[] limitToJob,
                                  char[] limitToType,
                                  boolean showDeleted,
                                  User user) throws SQLException
    {
        StringBuffer buf = new StringBuffer("");
        boolean hasLimitRole = false;
        boolean hasLimitJob = false;
        boolean hasLimitToType = false;
        if (limitToRole != null && limitToRole.length != 0)
        {
            hasLimitRole = true;
        }
        if (limitToJob != null && limitToJob.length != 0)
        {
            hasLimitJob = true;
        }
        if (limitToType != null && limitToType.length != 0)
        {
            hasLimitToType = true;
        }

        buf.append("SELECT ");
        buf.append("t.task_tag AS TASK_TAG, ");
        buf.append("t.job_tag AS JOB_TAG ");
        buf.append("FROM ");
        buf.append("tms.tasks t, ");
        buf.append("tms.user_roles ur ");//new
        buf.append("WHERE ");
        if (!showDeleted)
        {
            buf.append("t.type != 'D' AND ");
        }
//				buf.append("t.job_tag = t.job_tag ");
        buf.append("ur.ROLE_ID = t.ROLE_ID ");//new
        buf.append(" AND ur.username = '").append(user.getUsername()).append("' ");//new
        if (hasLimitRole)
        {
            buf.append("AND ");
            buf.append("(");
            for (int i = 0; i < limitToRole.length; i++)
            {
                buf.append("t.ROLE_ID = '" + limitToRole[i] + "' ");
                if (i + 1 < limitToRole.length)
                {
                    buf.append("OR ");
                }
            }
            buf.append(") ");
        }
        if (hasLimitJob)
        {
            buf.append("AND ");
            buf.append("(");
            for (int i = 0; i < limitToJob.length; i++)
            {
                buf.append("t.JOB_TAG = '" + limitToJob[i] + "' ");    // changed s1 to t
                if (i + 1 < limitToJob.length)
                {
                    buf.append("OR ");
                }
            }
            buf.append(") ");
        }
        if (hasLimitToType)
        {
            buf.append("AND ");
            buf.append("(");
            for (int i = 0; i < limitToType.length; i++)
            {
                buf.append("t.type = '" + limitToType[i] + "' ");
                if (i + 1 < limitToType.length)
                {
                    buf.append("OR ");
                }
            }
            buf.append(") ");
        }
        //System.out.println(buf.toString());
        return buf.toString();
    }

    /**
     * Takes the ResultSet from the tasks query, returns an array of new Task objects.
     * <BR>
     * This is THE ONLY method in taskmaster that instantiates Task objects.<BR><BR>
     * <p/>
     * It is set up to be called at the two places when tasks need instantiating; to create those with statuses and to create those with statuses.
     * <BR><BR>
     *
     * @param tc Stored inside each Task.  Each Task contains a reference to the collection inside of which it resides.
     * @param rs The rows and rows and rows from the database that are
     */
    private Task[] createTaskArray(User user, TaskCollection tc, ResultSet rs, Date onlyForDate, boolean instantiateWithStates) throws SQLException
    {
        try
        {
            if (rs == null)
            {
                throw new SQLException("taskmaster.job.TaskBuilder:createTaskArray: ResultSet passed was null.");
            }
            rs.last();
            int rsSize = rs.getRow();
            rs.beforeFirst();
            Task[] tasks = new Task[rsSize];
            java.sql.Time cronStartTime = null;
            String category = null;
            char type = 'q';
            for (int i = 0; i < tasks.length && rs.next(); i++)
            {
                type = (char) rs.getCharacterStream("TYPE").read();
                if (instantiateWithStates)
                {
                    category = ll.determineCategory(rs.getInt("STATUS_ID"), type);
                    tasks[i] = new Task(rs.getString("TASK_TAG"),
                            rs.getString("JOB_TAG"),
                            tc,
                            rs.getInt("ROLE_ID"),
                            type,
                            user,
                            rs.getTime("START_TIME"),
                            rs.getTime("END_TIME"),
                            rs.getTime("CURRENT_TIME"),
                            onlyForDate,
                            rs.getInt("ITERATION"),
                            rs.getInt("STATUS_ID"),
                            category, ll.determineCatOrdering(category),
                            rs.getString("SOURCE"),
                            rs.getString("HOST"),
                            rs.getString("NOTE"),
                            rs.getString("DESCRIPTION"),
                            rs.getString("COMMAND"),
                            rs.getString("PREDECESSOR_TAG"),
                            rs.getTime("CRON_START_TIME"),
                            rs.getFloat("TIMEOUT"),
                            rs.getInt("RETRIES")
                    );
                } else
                {
                    category = ll.determineCategory(-100, type);
                    tasks[i] = new Task(rs.getString("TASK_TAG"),
                            rs.getString("JOB_TAG"),
                            tc,
                            rs.getInt("ROLE_ID"),
                            type,
                            user,
                            null, null, null, onlyForDate,
                            rs.getInt("ITERATION"), // DS added 030305 - iteration currently in Jobs table (ASSUMES TODAY, WHICH IS A BAD ASSUMPTION IF YOU WANT TO MAKE CHANGES TO NON-TODAY DATES IN TASKS THAT HAVEN'T BEEN RUN ON THAT DAY)
                            -100, category, ll.determineCatOrdering(category),
                            null, null, null,
                            rs.getString("DESCRIPTION"),
                            rs.getString("COMMAND"),
                            rs.getString("PREDECESSOR_TAG"),
                            rs.getTime("CRON_START_TIME"),
                            rs.getFloat("TIMEOUT"),
                            rs.getInt("RETRIES")
                    );    // TRY SENDING ONLYfORdATE AS NULL, BECAUSE TASKS WITH NO STATUS ARE DATE INDEPENDANT;  THIS IS CONFUSING THIS WAY
                }
                // add start_time to task as new dep if it exists
                if ((cronStartTime = rs.getTime("CRON_START_TIME")) != null)
                {
                    category = ll.determineCategory(-100, 'C');
                    java.text.SimpleDateFormat s = new java.text.SimpleDateFormat("HH:mm:ss");
                    // DARRELL 021221 - REPLACED THE NEXT LINE WITH THE LINE 2 DOWN.  NOW, TASKS OF TYPE 'C' ARE
                    // REFERENCED BY THE SAME KEY AS THEIR PREDECESSORS, SO THEY DON'T BOTH APPEAR IN LISTS
                    // THIS CHANGE SEEMS SAFE, BUT MAY CAUSE UNFORSEEN COMPLEXITIES. This was bug 769.
//					Task cronDep = new Task(s.format(cronStartTime,  new StringBuffer(), new java.text.FieldPosition(0)).toString(),
                    Task cronDep = new Task(tasks[i].getTaskTag(),
                            rs.getString("JOB_TAG"),
                            tc, rs.getInt("ROLE_ID"),
                            'C', user, null, null, null, onlyForDate,
                            rs.getInt("ITERATION"), // DS added 030305
                            -100,
                            category, ll.determineCatOrdering(category),
                            null, null, null, null, null, null,
                            cronStartTime, 0, 0);
                    tasks[i].addDependency(cronDep);
                    cronDep.addChild(tasks[i]);
                }
            }

            return tasks;
        } catch (IOException e)
        {
            String expl = "taskmaster.job.TaskBuilder:createTaskArray: " + e;
            System.err.println(expl);
            throw new SQLException(expl, e);
        } finally
        {
            rs.close();
        }
    }

    /**
     * Links all predecessors for each task in TMS (allTaskHash) together.
     * <BR><BR>
     * Links together all predecessors of all the Tasks in AllTaskHash
     * <BR><BR>
     * This is one of 2 methods that builds the tree of all tasks in TMS.
     *
     * @see taskmaster.job.Task
     */
    private void linkPredecessors(Hashtable<String, Task> allTasks)
    {
        Task parent = null;
        for (Task child : allTasks.values())
        {
            if ((parent = allTasks.get(child.getPredecessor())) != null)
            {
//				if (parent.getDescendant() != null)
//					System.err.println(parent +" has at least 2 other tasks pointing at it as the pred: \n\t"+ parent.getDescendant() +"\n\t"+ child +"\n");
//				parent.setDescendant(child);
                child.setPredecessor(parent);
                parent.addChild(child);
            } else if (!child.getPredecessor().equals(child.getJobTag() + ":null"))
            { // if task has pred
                System.err.println("in taskmaster.job.TaskBuilder:linkPredecessors(), needed predecessor " + child.getPredecessor() + " does not exist in allTaskHash. - This shouldn't be possible");
            }
        }
    }

    /**
     * Links all dependencies for each task in TMS (allTaskHash) together.
     * <BR><BR>
     * Links together each row in tms.task_dependencies to its task in allTaskHash.
     * <BR><BR>
     * This is one of 2 methods that builds the tree of all tasks in TMS.
     * <BR><BR>
     * For each row in tms.task_dependencies, add it to the internal vector inside of each Task in the passed taskHash
     *
     * @see taskmaster.job.Task
     */
    private void linkDependencies(User user, Hashtable<String, Task> allTaskHash) throws SQLException
    {
        PreparedStatement pstmtTaskDependencies = getPreparedStatement(user, "SELECT * FROM tms.task_dependencies where active=1");
        ResultSet rs = pstmtTaskDependencies.executeQuery();
        Task parent = null;
        Task child = null;

        while (rs.next())
        {
            if ((child = allTaskHash.get(rs.getString("JOB_TAG") + ":" + rs.getString("TASK_TAG"))) != null)
            { // if this task exists
                parent = allTaskHash.get(rs.getString("JOB_TAG") + ":" + rs.getString("DEPENDENCY_TAG"));
                child.addDependency(parent);    // add this dependency to this task (it's a parent of child)
                parent.addChild(child);            // add the child task as a child for the parent
            } else
            {
                System.err.println("in linkDependencies(), " + rs.getString("JOB_TAG") + ":" + rs.getString("TASK_TAG") + " does not exist in allTaskHash. - This shouldn't be possible");
            }
        }
        rs.close();
        pstmtTaskDependencies.close();
    }

    /**
     * Called after the tree of all the Tasks in TMS has been built to refresh what category they're in based on their dependencies/predecessors.
     * <BR><BR>
     * now that the tree has been built, recalculate all categories: go back through looking for Tasks waiting for user,
     * For all those Tasks, change them to 'blocked' if they have any deps or preds that aren't complete.
     * <BR><BR>
     * THIS COULD REPLACE THE ll.determineCategory() CALL MADE WHEN EACH TASK IS INSTANTIATED FOR OPTIMIZATION
     */
    private void updateCategories(Hashtable<String, Task> allTaskHash)
    {
        for (Task walker : allTaskHash.values())
        {
            walker.category = ll.determineCategory(walker.getStatusId(), walker.getType());
            walker.ordering = ll.determineCatOrdering(walker.category);
            if (!"needsUser".equals(walker.category))
            {
                continue;
            }
            if (walker.depsPredsCompleted())
            {
                continue;
            }
            walker.category = "blocked";
            walker.ordering = ll.determineCatOrdering(walker.category);
        }
    }


    /**
     * sets the starttime/endtime of each task in taskHash, allowing for elapsed time calculation for previous time this task was run.
     */
    private void setLastRunElapsedTime(User user, java.util.Date runDate, Hashtable<String, Task> taskHash) throws SQLException
    {
        PreparedStatement pstmtLastRunElapsedTime = null;
        try
        {
            if (taskHash == null || runDate == null)
            {
                return;
            }
            // pstmtLastRunElapsedTime = this.getPreparedStatement(user, "select t.JOB_TAG, t.TASK_TAG, t.START_TIME, t.END_TIME from tms.task_states t, tms.task_statuses s where s.STATUS_ID = t.STATUS_ID AND s.CATEGORY = 'completed' AND t.START_TIME is not null AND t.END_TIME is not null AND t.RUN_DATE >= ( ? - 7) AND t.RUN_DATE = (select max(t2.RUN_DATE) from tms.task_states t2 where t2.task_tag = t.task_tag AND t2.job_tag = t.job_tag AND t2.START_TIME is not null AND t2.END_TIME is not null AND t2.RUN_DATE >= ( ? -7))");
            pstmtLastRunElapsedTime = this.getPreparedStatement(user, "select t.JOB_TAG, t.TASK_TAG, avg(t.END_TIME - t.START_TIME)*24*60 as ELAPSED_TIME from tms.task_states t, tms.task_statuses s where s.STATUS_ID = t.STATUS_ID AND s.name in ('SUCCEEDED','CONTINUE_ANYWAY','SIGNED_OFF') AND t.START_TIME is not null AND t.END_TIME is not null AND t.RUN_DATE >= ( ? - 7) group by t.JOB_TAG, t.TASK_TAG");
            java.sql.Date d = new java.sql.Date(runDate.getTime());
            pstmtLastRunElapsedTime.setDate(1, d);
            // this.pstmtLastRunElapsedTime.setDate(2, d);
            ResultSet rs = pstmtLastRunElapsedTime.executeQuery();
            Task refTask;
            while (rs.next())
            {
                final String jobTag = rs.getString("JOB_TAG");
                if ((refTask = taskHash.get(jobTag + ":" + rs.getString("TASK_TAG"))) != null)
                {
                    // refTask.setLastRunDuration(rs.getFloat("START_TIME")-rs.getFloat("END_TIME"));
                    refTask.setLastRunDuration(rs.getFloat("ELAPSED_TIME"));
                }
            }
        } catch (SQLException e)
        {
            String expl = "taskmaster.job.TaskBuilder:setLastRunElapsedTime(): " + e;
            System.err.println(expl);
            throw new SQLException(expl, e);
        }
        finally
        {
            if (pstmtLastRunElapsedTime != null)
            {
                pstmtLastRunElapsedTime.close();
            }
        }
    }

    /**
     * Turns an array of Tasks into a hash table.
     * <BR>The hash is created with key=Task.task_tag value=Task
     *
     * @param taskArray an array of Tasks
     * @return a hashtable of those tasks
     */
    private Hashtable<String, Task> createTaskHash(Task[] taskArray)
    {
        if (taskArray == null || taskArray.length == 0)
        {
            throw new IllegalStateException("taskmaster.job.TaskBuilder:createTaskHash: Cannot instantiate a collection of Tasks with null or empty tasks array.");
        }
        Hashtable<String, Task> taskHash = new Hashtable<String, Task>(taskArray.length);
        for (int i = 0; i < taskArray.length; i++)
        {
            if (taskArray[i] == null)
            {
                throw new IllegalStateException("taskmaster.job.TaskBuilder:createTaskHash: Task at position " + i + " in param array was null");
            }
            taskHash.put(taskArray[i].getKey(), taskArray[i]);
        }
        return taskHash;
    }

    /**
     * takes hash of all tasks and updates it with the Tasks in the array.
     * <BR><BR>
     * Tasks in allStatuses have statuses, allTaskHash are all Tasks without statuses.
     * After method allTaskHash should be used and allStatuses array should be discarded
     * Matches on jobTag/taskTag pair (task.getKey()).
     * <BR><BR>
     *
     * @param taskHash    Tasks with statuses that are to be updated (They have no status)
     * @param statusArray Array of Tasks with statuses; they replace their copies in taskHash
     */
    private void replaceAllTasksWithStatuses(Hashtable<String, Task> taskHash, Task[] statusArray) throws SQLException
    {
        if (taskHash == null)
        {
            throw new SQLException("taskmaster.job.TaskBuilder:updateTaskHashWithStatuses: taskHash or statusArray args were null");
        }
        if (statusArray == null)
        {
            return;
        }
        for (int i = 0; i < statusArray.length; i++)
        {                        // for all statuses
            if (taskHash.get(statusArray[i].getKey()) != null)
            {        // find the task in the hash
                taskHash.remove(statusArray[i].getKey());                // remove orig Task (it doesn't have status)
                taskHash.put(statusArray[i].getKey(), statusArray[i]);    // replace it with the one from status array (it's uptodate with its status)
            } else
            {
                System.err.println("in replaceAllTasksWithStatuses(), " + statusArray[i].getKey() + " exists in status array but not in allTaskHash. - How is this possible?");
            }
        }
    }

    /**
     * Takes the result from executing the query generated by buildTaskIdSql() and returns a hashtable of all the Tasks that are in that resultset, and in limitToCategory.
     *
     * @see this.buildTaskIdSql()
     */
    private Hashtable<String, Task> limitToRequestedTasks(Hashtable<String, Task> allTaskHash, ResultSet rs, Set<String> limitToCategory) throws SQLException
    {
        Hashtable<String, Task> limitedTaskHash = new Hashtable<String, Task>();
        Task refTask;
        while (rs.next())
        {
            final String job = rs.getString("JOB_TAG");
            final String task = rs.getString("TASK_TAG");
            final Task task1 = refTask = allTaskHash.get(job + ":" + task);
            if (task1 == null)
            {
                final String message = "in limitToRequestedTasks(), " + job + ":" + task + " exists in db as one matching users request but not in allTaskHash. - How is this possible?";
                System.err.println(message);
                throw new IllegalStateException(message);
            }
            if (limitToCategory == null || limitToCategory.isEmpty())
            {
                limitedTaskHash.put(refTask.getKey(), refTask);
            } else if (limitToCategory.contains(refTask.getQuickCategory()))
            {
                limitedTaskHash.put(refTask.getKey(), refTask);
            }
        }
        rs.close();
        return (limitedTaskHash.size() == 0) ? null : limitedTaskHash;
    }

    /**
     * Update the collection, instead of recreating the whole thing (the tree, the tasks, limited, etc) from scratch.
     * <BR><BR>
     * This optimizes updates to the collection since they're called much more frequently on the same query than new queries are made.
     *
     * @return the number of tasks that originally had statuses and got updated
     *         <p/>
     *         incremental update of collection statuses
     */
    public int updateExistingCollection(TaskCollection tc, User user)
    {
        ResultSet rs = null;
        PreparedStatement allTasksWithStatuses = null;
        try
        {
            log(" -UPDATING collection (not full recreate)");
            QueryParams limiters = tc.getLimiters();
            // Get all the statuses for that date
            // assume that this.allTasksWithStatuses has been created, since the collection has been created and you're just updating it
            java.sql.Date d = new java.sql.Date(limiters.getDate().getTime());
            String sqlAllTasksWithStatuses = this.buildTaskCollectionSql(true, limiters.getShowDeleted());
            allTasksWithStatuses = this.getPreparedStatement(user, sqlAllTasksWithStatuses);

            allTasksWithStatuses.setDate(1, d);
            // rs = rsAllTasksWithStatuses
            rs = allTasksWithStatuses.executeQuery();
            log(" -retrieved all tasks with statuses");

            String category = null;
            int oldTasksUpdatedStatus = 0;
            while (rs.next())
            {
                Task refTask = null;
                final String job_tag = rs.getString("JOB_TAG");
                final String task_tag = rs.getString("TASK_TAG");
                final String key = job_tag + ":" + task_tag;
                if ((refTask = this.allTaskHashSaved.get(key)) != null)
                {
                    category = ll.determineCategory(rs.getInt("STATUS_ID"), refTask.getType());
                    if (refTask.getStartTime() != null)    // if task had status before and is being updated (which method called here doesn't matter, only that its something only a Task w/ status would have as not null
                    {
                        oldTasksUpdatedStatus++;
                    }
                    refTask.update(rs.getTime("START_TIME"),
                            rs.getTime("END_TIME"),
                            rs.getTime("CURRENT_TIME"),
                            rs.getInt("STATUS_ID"),
                            category, ll.determineCatOrdering(category),
                            rs.getString("SOURCE"),
                            rs.getString("HOST"),
                            rs.getString("NOTE"));
                } else
                {
                    System.err.println("in limitToRequestedTasks(), " + job_tag + ":" + task_tag + " exists in db as one matching users request but not in allTaskHash. - How is this possible?");
                }
            }

            // update categories because parent's statuses may be new
            this.updateCategories(this.allTaskHashSaved);

            // **** check this tomorrow *****
            Set<String> limitToCategories = limiters.getLimitToCategory();
            tc.limitAfterUpdate(limitToCategories);

            return oldTasksUpdatedStatus;
        } catch (Exception e)
        {
            System.err.println(e.toString());
            throw new RuntimeException(e);
        } finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException e) { }
            }
            if (allTasksWithStatuses != null)
            {
                try { allTasksWithStatuses.close(); } catch (SQLException e) { }
            }

        }
    }

    /**
     * An update fails when a task goes from having a status to not having a status.
     * <BR><BR>because the lack of a status does not show up in the db, and thus does not get selected for updating.
     * <BR>This check catches that circumstance, and fixes it.<BR>
     * The check is to see if the number of tasks that had statuses and got updated is the same as the number of tasks that originally had statuses when the collection was built.
     * <BR>
     * accessor method for private methods in this class; calls them in sequence, <BR>
     * a TaskBuilder is bound to a session, 1 per session as instantiated by ViewTasks
     */
    public TaskCollection getTaskCollection(QueryParams limiters, User user) throws SQLException
    {
        // initialize here so that queries can be seen by exception
        TaskCollection tc = new TaskCollection();
        // GET ALL THE TASKS
        String sqlAllTasks = this.buildTaskCollectionSql(false, limiters.getShowDeleted());
        PreparedStatement allTasks = this.getPreparedStatement(user, sqlAllTasks);
        final String s = " -prepared all tasks pstmt";
        log(s);
        ResultSet rsAllTasks = allTasks.executeQuery();
        log(" -retrieved all tasks");
        Task[] allTaskArray = this.createTaskArray(user, tc, rsAllTasks, limiters.getDate(), false);
        allTasks.close();
        log(" -turned result into Tasks array");
        if (allTaskArray.length == 0)
        {
            throw new SQLException("A query for all tasks in tms returned no tasks.  Something is wrong; where did all the tasks go?!");
        }
        Hashtable<String, Task> allTaskHash = this.createTaskHash(allTaskArray);
        log(" -created allTaskHash from allTaskArray");
        // GET ALL THE STATUSES FOR THAT DATE
        String sqlAllTasksWithStatuses = this.buildTaskCollectionSql(true, limiters.getShowDeleted());
        PreparedStatement allTasksWithStatuses = this.getPreparedStatement(user, sqlAllTasksWithStatuses);
        log(" -prepared sqlAllTasksWithStatuses pstmt");
        java.sql.Date d = new java.sql.Date(limiters.getDate().getTime());
        allTasksWithStatuses.setDate(1, d);
        ResultSet rsAllTasksWithStatuses = allTasksWithStatuses.executeQuery();
        log(" -retrieved all tasks with statuses");
        {
            Task[] allTaskWithStatusesArray = this.createTaskArray(user, tc, rsAllTasksWithStatuses, limiters.getDate(), true);
            allTasksWithStatuses.close();
            log(" -turned it into an array");
            this.replaceAllTasksWithStatuses(allTaskHash, allTaskWithStatusesArray);
            log(" -replaced tasks with those that had statuses");
        }
        this.linkPredecessors(allTaskHash);
        log(" -linked all predecessors into tree");
        this.linkDependencies(user, allTaskHash);
        log(" -linked all dependencies into tree");
        this.updateCategories(allTaskHash);
        log(" -updated categories now that tree is built");
        String sqlAllMatchingTasks = this.buildTaskIdSql(limiters.getLimitToRole(), limiters.getLimitToJob(), limiters.getLimitToType(), limiters.getShowDeleted(), user);
        log(" -built sql for finding user's request");
        ResultSet tasksMatchingRequest = this.runStatement(user, sqlAllMatchingTasks);
        log(" -retrieved tasks requested by user");
        Hashtable<String, Task> limitedTaskHash = this.limitToRequestedTasks(allTaskHash, tasksMatchingRequest, limiters.getLimitToCategory());
        log(" -created final hashtable to match user's request");

        this.setLastRunElapsedTime(user, limiters.getDate(), limitedTaskHash);
        log(" -found and set elapsed times for requests Tasks last run time");

        tc.replaceTaskHash(limitedTaskHash, limiters);
        tc.replaceLookupLists(ll);
        log(" -updated tc; about to return successfully");
        this.allTaskHashSaved = allTaskHash;

        return tc;
    }

    private static final Format FORMAT = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss.SSS");

    public static void log(String s)
    {
        System.err.println(FORMAT.format(new Date()) + s);
    }

    /**
     * tests the creation of a TaskCollection as closely as possible
     * to what happens from the actual .jsp
     */

    public static void main(String[] args)
    {
        try
        {
            taskmaster.auth.Login l = new taskmaster.auth.Login();
            User u = new User();
            System.err.println("arg0: username (oracle)");
            System.err.println("arg1: password (oracle)");
            System.err.println("arg2: host (oci8)");
            System.err.println("arg3: database (prod, qa, dev)");
            System.err.println("arg0: username (oracle)");
            System.err.println("arg1: password (oracle)");
            System.err.println("arg2: host (oci8)");
            System.err.println("arg3: database (prod, qa, dev)");
            //	l.login(u, args[0], args[1], args[2], args[3]);
            TaskBuilder t = new TaskBuilder(new LookupLists(u));

            java.util.Calendar c = java.util.Calendar.getInstance();
            c.set(2002, 02, 26);
            //	QueryParams limit = new QueryParams(u, null, null, null, null, c.getTime(), false);
            //	TaskCollection tc = t.getTaskCollection(limit, new TaskCollection());

            System.err.println("This ran all the way through without Exception.");
        } catch (Exception e)
        {
            System.err.println("Exception in TaskBuilder:main: " + e);
            e.printStackTrace();
        }
    }
}
